-- =============================================
-- This stored procedure moves a specified Content File record.
--
-- Modifications
-- 08/28/2003  E.Tatsui   Created
-- =============================================
CREATE     PROCEDURE amsp_CMMoveContentFile
  @InMoveContentFileID numeric,
  @InTargetContentFileID numeric,
  @InContentID numeric
AS
BEGIN
  DECLARE
    @TargetSortOrder numeric,
    @ContentID numeric,
    @ContentFileID numeric,
    @SortOrder numeric,
    @Counter integer,
    @MoveContentFileID numeric,
    @TargetContentFileID numeric

  SELECT @TargetSortOrder = SortOrder,
         @ContentID = ContentID,
         @TargetContentFileID = ContentFileID
    FROM Content_File  WITH (NOLOCK)
   WHERE (ContentFileID = @InTargetContentFileID
      OR PreviousContentFileID = @InTargetContentFileID)
     AND ContentID = @InContentID

  SELECT @MoveContentFileID = ContentFileID
    FROM Content_File WITH (NOLOCK)
   WHERE (ContentFileID = @InMoveContentFileID
      OR PreviousContentFileID = @InMoveContentFileID)
     AND ContentID = @InContentID

  -- If we can't find either of the record, exit.
  IF @MoveContentFileID IS NULL OR @TargetContentFileID IS NULL
    RETURN

  -- Move everything below target by one.
  UPDATE Content_File
     SET SortOrder = SortOrder + 1
   WHERE ContentID = @ContentID
     AND SortOrder > @TargetSortOrder

  -- Move the request item right below the target.
  UPDATE Content_File
     SET SortOrder = @TargetSortOrder + 1
   WHERE ContentFileID = @MoveContentFileID

  -- Now, re-sort everything.
  DECLARE c_Files CURSOR FOR
   SELECT ContentFileID,
          SortOrder
     FROM Content_File WITH (NOLOCK)
    WHERE ContentID = @ContentID
    ORDER BY SortOrder

  SET @Counter = 0

  OPEN c_Files
  FETCH NEXT FROM c_Files
   INTO @ContentFileID,
        @SortOrder

  WHILE @@FETCH_STATUS = 0 BEGIN
    SET @Counter = @Counter + 1

    IF @Counter <> @SortOrder BEGIN
      UPDATE Content_File
         SET SortOrder = @Counter
       WHERE ContentFileID = @ContentFileID
    END

    FETCH NEXT FROM c_Files
     INTO @ContentFileID,
          @SortOrder
  END
  CLOSE c_Files
  DEALLOCATE c_Files
  
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMMoveContentFile] TO [IMIS]
GO